package org.myhibernate.excel;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import jxl.Sheet;
import jxl.Workbook;

public class ExcelReader 
{
	private int pages=0;
	private Workbook workbook=null;
	public List<List<String>> readReturnListInList(String excelFilePath)throws Exception
	{
		try {
			return readReturnListInList(excelFilePath, 0);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e.toString());
		}
	}
	public List<List<String>> readReturnListInList(String excelFilePath,int sheetIndex)throws Exception
	{
		String ext=excelFilePath.substring(excelFilePath.lastIndexOf(".")+1).toLowerCase();
		try {
			if ("xlsx".equalsIgnoreCase(ext)) 
			{
				return readReturnListInList07(excelFilePath,sheetIndex);
			}
			return readReturnListInList03(excelFilePath,sheetIndex);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e.toString());
		}
	}
	public List<List<String>> readReturnListInList03(String excelFilePath,int sheetIndex)throws Exception
	{
		List<List<String>> list=new ArrayList<List<String>>();
		try {
			workbook = Workbook.getWorkbook(new FileInputStream(excelFilePath));
		} catch (Exception e)
		{
			System.out.println("Read error :"+excelFilePath);
			e.printStackTrace();
			throw new RuntimeException(e.getCause());
		}
		pages=workbook.getNumberOfSheets();
		Sheet sheet=workbook.getSheet(sheetIndex);
		int row=sheet.getRows();
		int col=sheet.getColumns();
		//read  head
		for (int i = 0; i < row; i++)
		{
				ArrayList<String> contentList=new ArrayList<String>();
				for (int j = 0; j < col; j++)
				{
					contentList.add(sheet.getCell(j,i).getContents());
				}//end for
				list.add(contentList);
		}//end for
		if (workbook!=null) {
			workbook.close();
		}
		return list;
	}
	public List<List<String>> readReturnListInList07(String excelFilePath,int sheetIndex)throws Exception
	{
		List<List<String>> list=new ArrayList<List<String>>();
		//read excel
		InputStream is=new FileInputStream(excelFilePath);
		XSSFWorkbook xwb = new XSSFWorkbook(is);
		pages=xwb.getNumberOfSheets();
		XSSFSheet sheet=xwb.getSheetAt(sheetIndex);
		int rows=sheet.getLastRowNum()+1;
		int cols=0;
		XSSFRow row=null;
		for (int i = 0; i < rows; i++) 
		{
			row=sheet.getRow(i);
			cols=row.getLastCellNum();
			List<String> dataList=new ArrayList<String>();
			for (int j = 0; j < cols; j++) 
			{
				XSSFCell cell= row.getCell(j);
				if (cell!=null)
				{
					if(cell.getCellType() == XSSFCell.CELL_TYPE_STRING)
					{ 
						dataList.add( row.getCell(j).getStringCellValue());
					}else if(cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC)
					{ 
						dataList.add( row.getCell(j).getNumericCellValue()+"");
					}else if(cell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN)
					{ 
						dataList.add( row.getCell(j).getBooleanCellValue()+"");
					}else {
						dataList.add("");
					}
				} else
				{
					dataList.add("");
				}
			}
			list.add(dataList);
		}
		try {
			is.close();
		} catch (Exception e) {
			throw new RuntimeException(e.toString());
		}
		return list;
	}
	public List<Map<String,String>> readReturnMapInList(String excelFilePath)throws Exception
	{
		try {
			return readReturnMapInList(excelFilePath, 0);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e.toString());
		}
	}
	public List<Map<String,String>> readReturnMapInList(String excelFilePath,int sheetIndex)throws Exception
	{
		String ext=excelFilePath.substring(excelFilePath.lastIndexOf(".")+1).toLowerCase();
		try {
			if ("xlsx".equalsIgnoreCase(ext)) 
			{
				return readReturnMapInList07(excelFilePath,sheetIndex);
			}
			return readReturnMapInList03(excelFilePath,sheetIndex);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e.toString());
		}
	}
	public List<Map<String,String>> readReturnMapInList03(String excelFilePath,int sheetIndex)throws Exception
	{
		List<Map<String,String>> list=new ArrayList<Map<String,String>>();
		try {
			workbook = Workbook.getWorkbook(new FileInputStream(excelFilePath));
		} catch (Exception e)
		{
			System.out.println("Read error :"+excelFilePath);
			e.printStackTrace();
			throw new RuntimeException(e.toString());
		}
		pages=workbook.getNumberOfSheets();
		Sheet sheet=workbook.getSheet(sheetIndex);
		int row=sheet.getRows();
		int col=sheet.getColumns();
		//read  head
		List<String> headList=new ArrayList<String>();
		String cellData="";
		for (int i = 0; i < row; i++)
		{
				if (0 ==i)
				{
					for (int j = 0; j < col; j++)
					{
						String headColumn=sheet.getCell(j,i).getContents()+"";
						headColumn=headColumn.replace(" ", "");
						headList.add(headColumn.trim());
					}//end for
				} else
				{
					Map<String,String> contentMap=new HashMap<String,String>();
					boolean isEmpty=false;
					for (int j = 0; j < col; j++)
					{
						cellData=sheet.getCell(j,i).getContents();
						if ((cellData!=null)&&(!cellData.trim().equalsIgnoreCase("null"))&&(!cellData.trim().equals(""))) {
							isEmpty=true;
						}
						cellData=cellData+"";
						cellData=cellData.replace(" ", "");
						contentMap.put(headList.get(j), cellData);
					}//end for
					if (isEmpty) {
						list.add(contentMap);
					}
				}
				
		}//end for
		if (workbook!=null) {
			workbook.close();
		}
		return list;
	}
	public List<Map<String,String>> readReturnMapInList07(String excelFilePath,int sheetIndex)throws Exception
	{
		List<Map<String,String>> list=new ArrayList<Map<String,String>>();
		//read excel
		InputStream is=new FileInputStream(excelFilePath);
		XSSFWorkbook xwb = new XSSFWorkbook(is);
		pages=xwb.getNumberOfSheets();
		XSSFSheet sheet=xwb.getSheetAt(sheetIndex);
		int rows=sheet.getLastRowNum()+1;
		int cols=0;
		Map<String, String> headMap=new HashMap<String, String>();
		XSSFRow row= sheet.getRow(0);
		cols=row.getLastCellNum();
		for (int i = 0; i < cols; i++) 
		{
			headMap.put(i+"", row.getCell(i).getStringCellValue());
		}
		for (int i = 1; i < rows; i++) 
		{ 
			row=sheet.getRow(i);
			cols=row.getLastCellNum();
			Map<String, String> dataMap=new HashMap<String, String>();
			for (int j = 0; j < cols; j++) 
			{
				XSSFCell cell= row.getCell(j);
				if (cell!=null)
				{
					if(cell.getCellType() == XSSFCell.CELL_TYPE_STRING)
					{ 
						dataMap.put(headMap.get(j+""), row.getCell(j).getStringCellValue());
					}else if(cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC)
					{ 
						dataMap.put(headMap.get(j+""), row.getCell(j).getNumericCellValue()+"");
					}else if(cell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN)
					{ 
						dataMap.put(headMap.get(j+""), row.getCell(j).getBooleanCellValue()+"");
					}else {
						dataMap.put(headMap.get(j+""),"");
					}
				} else
				{
					dataMap.put(headMap.get(j+""),"");
				}
			}
			list.add(dataMap);
		}
		try {
			is.close();
		} catch (Exception e) {
			throw new RuntimeException(e.toString());
		}
		return list;
	}
	public int getPages() {
		return pages;
	}
	public void close()
	{
		if (workbook!=null) {
			try {
				workbook.close();
			} catch (Exception e) {
			}
		}
	}
	public static void main(String[] args) throws Exception
	{
		String excelFilePath="e:/test.xlsx";
		ExcelReader reader=new ExcelReader();
		//List<List<String>> list=reader.readReturnhListInList(excelFilePath);
		List list2=reader.readReturnMapInList(excelFilePath);
		
		System.out.println("content="+list2);
		
		reader.close();
	}
}
